unit R_CopiasPorDocumento;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ExtCtrls, QuickRpt, DB, ZAbstractRODataset, ZDataset, QRCtrls;

type
  TR_CopiaPorDocumento = class(TForm)
    QuickRep1: TQuickRep;
    QRBand1: TQRBand;
    QRImage2: TQRImage;
    QRLabel4: TQRLabel;
    QRImage1: TQRImage;
    QRLabel2: TQRLabel;
    QRGroup2: TQRGroup;
    QRExpr2: TQRExpr;
    ZR_Consulta: TZReadOnlyQuery;
    QRPageFooter: TQRBand;
    QRLabel5: TQRLabel;
    QRLabel6: TQRLabel;
    QRSysData1: TQRSysData;
    QRGroup1: TQRGroup;
    QRExpr1: TQRExpr;
    QRDBText1: TQRDBText;
    TitleBand1: TQRBand;
    QRLabel1: TQRLabel;
    QRLabel3: TQRLabel;
    QRLabel7: TQRLabel;
    QRLabel8: TQRLabel;
    QRBand2: TQRBand;
    QRDBText2: TQRDBText;
    QRLabel9: TQRLabel;
  private
    { Private declarations }
  public
    { Public declarations }
    constructor create(AOwner: TComponent; Documentos: String);
  end;

implementation

uses DM_Persistencia;

{$R *.dfm}

constructor TR_CopiaPorDocumento.create(AOwner: TComponent; Documentos: String);
var
  s:string;
begin
  inherited create(AOwner);

  s:= 'SELECT DOCS.ID AS ID_DOC, TITULO, COALESCE (DENOMINACION, '+QuotedStr('Materia no disponible')+') AS MATERIA, COALESCE (CUENTA, 0) AS NRO_COPIAS, TOTAL_COPIAS'+
      ' FROM DOCUMENTOS DOCS'+
      ' LEFT JOIN'+
      ' (SELECT FK_ID_DOCUMENTO, FK_ID_MATERIA, SUM (NRO_COPIAS) AS CUENTA'+
      ' FROM DETALLE_PEDIDO'+
      ' GROUP BY FK_ID_DOCUMENTO, FK_ID_MATERIA) AS CNTA ON DOCS.ID = CNTA.FK_ID_DOCUMENTO'+
      ' LEFT JOIN MATERIAS MAT ON MAT.ID = CNTA.FK_ID_MATERIA'+
      ' LEFT JOIN (SELECT FK_ID_DOCUMENTO, SUM (NRO_COPIAS) AS TOTAL_COPIAS'+
         ' FROM DETALLE_PEDIDO'+
         ' WHERE FK_ID_DOCUMENTO IN ('+Documentos+')'+
         ' GROUP BY FK_ID_DOCUMENTO) AS TOT ON TOT.FK_ID_DOCUMENTO = DOCS.ID'+
      ' WHERE DOCS.ID IN ('+Documentos+')'+
      ' ORDER BY TITULO, MATERIA';

  with ZR_Consulta do
    begin
      SQL.Clear;
      Close;
      SQL.Add(s);
      Open;
    end;


end;

end.
